﻿using MXComponent.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace MXComponent.DAL
{
    public partial class formulaDB
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public formulaDB()
        {
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">model</param>
        public int AddRecord(formulaData model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("set nocount on; ");
            strSql.Append("insert into formula(");
            strSql.Append(@"formulaname,formuladesc,active,createtime,creatby)");
            strSql.Append(" values (");
            strSql.Append(@"@formulaname,@formuladesc,@active,@createtime,@creatby)");
            strSql.Append("; select @@identity; set nocount off; ");
            SqlParameter[] parameters = {
                    new SqlParameter("@formulaname", SqlDbType.NVarChar,50),
                    new SqlParameter("@formuladesc", SqlDbType.NVarChar,250),
                    new SqlParameter("@active", SqlDbType.Int),
                    new SqlParameter("@createtime", SqlDbType.DateTime),
                    new SqlParameter("@creatby", SqlDbType.Int)
                };
            parameters[0].Value = model.formulaname;
            parameters[1].Value = model.formuladesc;
            parameters[2].Value = model.active;
            parameters[3].Value = model.createtime == string.Empty ? null : model.createtime;
            parameters[4].Value = model.creatby;

            int id = 0;
            object ret = SqlHelper.ExecuteScalar( CommandType.Text, strSql.ToString(), parameters);

            if (ret != null && ret != DBNull.Value)
            {
                id = int.Parse(ret.ToString());
            }
            return id;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="model">model</param>
        public void ModifyRecord(formulaData model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            strSql.Append("update formula set ");

            Dictionary<string, SqlParameter> dic = new Dictionary<string, SqlParameter>();
            dic.Add("formulaid", new SqlParameter("@formulaid", SqlDbType.Int));

            if (model.formulaname != null && model.formulaname != string.Empty)
            {
                if (strSql1 != null && strSql1.ToString() != string.Empty)
                {
                    strSql1.Append(", formulaname=@formulaname");
                }
                else
                {
                    strSql1.Append("formulaname=@formulaname");
                }
                dic.Add("formulaname", new SqlParameter("@formulaname", SqlDbType.NVarChar));
            }
            if (model.formuladesc != null && model.formuladesc != string.Empty)
            {
                if (strSql1 != null && strSql1.ToString() != string.Empty)
                {
                    strSql1.Append(", formuladesc=@formuladesc");
                }
                else
                {
                    strSql1.Append("formuladesc=@formuladesc");
                }
                dic.Add("formuladesc", new SqlParameter("@formuladesc", SqlDbType.NVarChar));
            }
            if (model.active != -1)
            {
                if (strSql1 != null && strSql1.ToString() != string.Empty)
                {
                    strSql1.Append(", active=@active");
                }
                else
                {
                    strSql1.Append("active=@active");
                }
                dic.Add("active", new SqlParameter("@active", SqlDbType.Int));
            }
           
            if (model.updatetime != null && model.updatetime != string.Empty)
            {
                if (strSql1 != null && strSql1.ToString() != string.Empty)
                {
                    strSql1.Append(", updatetime=@updatetime");
                }
                else
                {
                    strSql1.Append("updatetime=@updatetime");
                }
                dic.Add("updatetime", new SqlParameter("@updatetime", SqlDbType.DateTime));
            }
            if (model.updateby != -1)
            {
                if (strSql1 != null && strSql1.ToString() != string.Empty)
                {
                    strSql1.Append(", updateby=@updateby");
                }
                else
                {
                    strSql1.Append("updateby=@updateby");
                }
                dic.Add("updateby", new SqlParameter("@updateby", SqlDbType.Int));
            }


            strSql.Append(strSql1);
            strSql.Append(" where formulaid = @formulaid ");

            SqlParameter[] parameters = null;

            if (dic.Count > 0)
            {
                parameters = new SqlParameter[dic.Count];

                int i = 0;
                BindingFlags flag = BindingFlags.Public | BindingFlags.IgnoreCase | BindingFlags.Instance;
                foreach (KeyValuePair<string, SqlParameter> obj in dic)
                {
                    parameters[i] = obj.Value;
                    PropertyInfo propinfo = model.GetType().GetProperty(obj.Key, flag);
                    //if (propinfo.PropertyType.FullName.ToUpper().CompareTo("SYSTEM.GUID") == 0)
                    //{
                    //    if ((Guid)propinfo.GetValue(model, null) == Guid.Empty)
                    //    {
                    //        parameters[i].Value = DBNull.Value;
                    //    }
                    //    else
                    //    {
                    //        parameters[i].Value = propinfo.GetValue(model, null);
                    //    }
                    //}
                    //else
                    //{
                    //    parameters[i].Value = propinfo.GetValue(model, null);
                    //}
                    parameters[i].Value = propinfo.GetValue(model, null);
                    i++;
                }
            }

            SqlHelper.ExecteNonQuery( CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="formulaid">主键值</param>
        public void DeleteRecord(int formulaid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update  formula set active=0");
            strSql.Append(" where formulaid = @formulaid ");
            SqlParameter[] parameters = {
                    new SqlParameter("@formulaid", SqlDbType.Int)
                };
            parameters[0].Value = formulaid;

            SqlHelper.ExecteNonQuery( CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到一个model
        /// </summary>
        /// <param name="formulaid">主键值</param>
        /// <returns>model</returns>
        public formulaData GetModel(int formulaid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select formulaid,formulaname,formuladesc,active,createtime,creatby,updatetime,updateby from formula");
            strSql.Append(" where formulaid = @formulaid ");
            SqlParameter[] parameters = {
                    new SqlParameter("@formulaid", SqlDbType.Int)
                };
            parameters[0].Value = formulaid;

            formulaData model = new formulaData();
            DataSet ds = SqlHelper.ExecuteDataSet(CommandType.Text, strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow row = ds.Tables[0].Rows[0];
                if (row["formulaid"] != DBNull.Value)
                {
                    model.formulaid = Convert.ToInt32(row["formulaid"].ToString());
                }
                if (row["formulaname"] != DBNull.Value)
                {
                    model.formulaname = Convert.ToString(row["formulaname"].ToString());
                }
                if (row["formuladesc"] != DBNull.Value)
                {
                    model.formuladesc = Convert.ToString(row["formuladesc"].ToString());
                }
                if (row["active"] != DBNull.Value)
                {
                    model.active = Convert.ToInt32(row["active"].ToString());
                }
                if (row["createtime"] != DBNull.Value)
                {
                    model.createtime = Convert.ToString(row["createtime"].ToString());
                }
                if (row["creatby"] != DBNull.Value)
                {
                    model.creatby = Convert.ToInt32(row["creatby"].ToString());
                }
                if (row["updatetime"] != DBNull.Value)
                {
                    model.updatetime = Convert.ToString(row["updatetime"].ToString());
                }
                if (row["updateby"] != DBNull.Value)
                {
                    model.updateby = Convert.ToInt32(row["updateby"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="strWhere">限制条件</param>
        /// <returns>DataSet</returns>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select formulaid,formulaname,formuladesc,active,createtime,creatby,updatetime,updateby from formula");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by formulaid");
            return SqlHelper.ExecuteDataSet( CommandType.Text, strSql.ToString());
        }

        public DataSet GetAllMaterial()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@" select 0 as formuladetailid, w.warehouseid,w.warehousename,w.materialid,m.materialname,0 as planqty,0 as errorqty  from dbo.materialwarehouse w left join dbo.material m on w.materialid=m.materialid where w.active=1");          
            return SqlHelper.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        public DataSet GetAllMaterialUpdate(int? formulaid)
        {
            StringBuilder strSql = new StringBuilder();
            // strSql.AppendFormat(@" select d.formuladetailid,w.warehouseid,w.warehousename,w.materialid,m.materialname,d.planqty,d.errorqty from dbo.formuladetail d left join dbo.materialwarehouse w on d.warehouseid=w.warehouseid left join dbo.material m on w.materialid=m.materialid where formulaid={0}",formulaid);

            strSql.AppendFormat(@" select d.formuladetailid,w.warehouseid,w.warehousename,w.materialid,m.materialname,ISNULL( d.planqty,0) planqty,ISNULL(d.errorqty,0 ) errorqty 
 from dbo.materialwarehouse w 
 left join  (select formuladetailid,planqty, errorqty,warehouseid from  dbo.formuladetail where formulaid={0}) d on d.warehouseid=w.warehouseid 
 left join dbo.material m on w.materialid=m.materialid 
 where w.active='1' order by w.warehouseno", formulaid);
           return SqlHelper.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }
    }
}